The Unpivot node is used to transform matrix grids into tabular grid. You may find that you have matrix grids in your data source; in this case, you'll want to 'unpivot' them so that each column can then be added to the Pyramid data model as a separate hierarchy or measure.
Transforming your matrix grids into tabular ones is a simple process in Pyramid, which is achieved by attaching the Unpivot node to the table node within which the matrix grid resides.
How to Unpivot a Table
Here, we have a spreadsheet containing a matrix grid.
When the table is configured in the ETL, we see in the Preview panel that we need to configure some changes in order to transform the table into something that can be used to build queries:
In order to transform the matrix grid into a tabular format, the Unpivot node must be added. Find the Unpivot node in the Preparations tab:
Connect the Unpivot node to the table node containing the matrix grid, and with the Unpivot node selected, click Settings from the Properties panel:
You will be presented with the Unpivot Settings dialog, when you must select which rows and columns should be converted into columns:
If, in the datasource, the first row in the matrix grid contains column headers (as is the case in this example), select 'Column Names as First Row' to show the column names (green highlight below).
In this example, we have 2 columns consisting of row headers, which we want to convert into columns: dateKey and Product Category. These are the 2 left-most columns which are selected and shaded in blue in the image below.
We also have a row of column headers along to top, which we want to convert into the Manufacturers columns. And in the second row, we have measures; select the measures icon (orange arrow below) to mark a measure column. You can only choose one measure column, but all measures in the chosen row or column will be extrapolated into logical columns. For instance, the selection below will produce 2 measure columns: net profit and expenses.
Click Preview (yellow arrow) to see a preview of the results.
Your selections can be seen in the flat results preview. Here, you can edit the column names, and hide columns:
After clicking Apply, we can see the flattened columns when previewing the Unpivot node:
